[ psqlodbc-Bugs-1000681 ] Error when using ODBC driver with Microsoft Access

Bugs item #1000681, was opened at 2006-07-06 13:54
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 81&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Nobody (None)
Assigned to: Nobody (None)
Summary: Error when using ODBC driver with Microsoft Access

Initial Comment:
I have a similar problem as http://pgfoundry.org/tracker/index.php?func=detail&aid=10005 28&group_id=1000125&atid=538, although not after a foreign key error, it just occurs at random multiple times a day.

I am using Access 2002, with tables link to PostgreSQL using version 8.01.02.00 ANSI of the ODBC driver. After logging in to Access, everything will work without problem for some time, and then just suddenly fail.

Once the problem occurs any table I open contains the correct number of rows, but ever column contains the text '#Name?'. And I will get something similar to the following in the postgreSQL logs:

postgres[19930]: [22-1] 2006-07-06 12:33:51 BST guycallaghan research ERROR: invalid input syntax for integer: "^C"
postgres[19930]: [22-2] 2006-07-06 12:33:51 BST guycallaghan research STATEMENT: SELECT
postgres[19930]: [22-3] "codeid","colourcode","hexvalue","image","timestamp","userid " FROM "admin"."code" WHERE "codeid" = '^C' OR
postgres[19930]: [22-4] "codeid" = '^A' OR "codeid"= '^B' OR "codeid" = '^D' OR "codeid" = '^D' OR "codeid" = '^D' OR "codeid" = '^D' OR
postgres[19930]: [22-5] "codeid" = '^D' OR "codeid"= '^D' OR "codeid" = '^D'


Restarting Access always resolves the problem.




------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
> [url=http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
][/url] > [url=http://cholesterolgood.page.by/cholestae/cholesterol-di et-healthy-high.html cholesterol diet healthy high
][/url] > [url=http://cholesterolgood.page.by/cholestae/cholesterol-al ternative-treatments.html cholesterol alternative treatments
][/url] > [url=http://cholesterolgood.page.by/cholestae/cholesterol-al ternative-treatment.html cholesterol alternative treatment
][/url] > [url=http://cholesterolgood.page.by/cholestae/low-cholestero l-meal-plan.html low cholesterol meal plan
][/url]

------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
> [url=http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
][/url] > [url=http://cholesterolgood.page.by/cholestae/cholesterol-di et-healthy-high.html cholesterol diet healthy high
][/url] > [url=http://cholesterolgood.page.by/cholestae/cholesterol-al ternative-treatments.html cholesterol alternative treatments
][/url] > [url=http://cholesterolgood.page.by/cholestae/cholesterol-al ternative-treatment.html cholesterol alternative treatment
][/url] > [url=http://cholesterolgood.page.by/cholestae/low-cholestero l-meal-plan.html low cholesterol meal plan
][/url]

------------------------------------------------------------ ----------

Comment By: Greg Campbell (gregc)
Date: 2006-08-09 13:09

Message:
I highly recommend ConnectionTimeout of 0 where pgodbc is used with Jet/Access. That means never timeout. Many problems seem to disappear.

Your latest posting seems to imply a timeout factor, and a multi-thread problem, possibly a connection pooling issue. One thread seems to stay alive, while a secondary thread, used after a timeout, fails to reconnect, or more correctly it is a thread that tries to reuse a connection that has timed out. It obviously is unaware of the timeout or the Access code is ill-prepared to handle the timeout on that thread. This means the only thing you can do is to not allow the timeouts.

You might also consider getting an account/logging in on pgfoundry so that we have some name to address you by.



------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-08-09 09:02

Message:
Greg,


The standard procedure for Access when you open a table appears to be ... First it fetches a list of primary keys with one query. It then follows-up with a second query to fetch the actual full row data.

This procedure is the same whether the connection is the first one, or a re-created one after the timeout.


When I capture the network traffic, I can see the first query always works, irrespective of whether it is the intital database connection, or one re-connected after a timeout.

It is the second query that fails when we have a re-connected connection.


The default setting for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\Co nnect
ionTimeout is 600 (i.e. 10 minutes). I have increased ours to 3600 (i.e. one hour).






------------------------------------------------------------ ----------

Comment By: Greg Campbell (gregc)
Date: 2006-08-07 14:42

Message:
You indicate a time factor, and after that Access sends these querie do not use the primary keys and queries each row by all possible values. Yes?

What is your setting for
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\Co nnectionTimeout



------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-08-07 11:24

Message:
Hi Luf,

I eliminated the protocol change (see comment 2006-07-10 11:52), but the problem still occurred.

I will try the latest dll as suggested.


For anybody else suffering with this problem, you can reduce the impact by extending the Jet ODBC connection timeout by editing the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\C onnectionTimeout



------------------------------------------------------------ ----------

Comment By: Ludek Finstrle (luf)
Date: 2006-07-24 10:49

Message:
Hello,

the change from 6.4 to 7.4 protocol is significant. You should use 7.4.

Could you try the latest dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?
If the problem still persist it would be nice you post us the mylog output (maybe only the problematic part - it depends on mylog size).

Regards,

Luf

------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-12 11:18

Message:
I captured the network traffic to see if I can spot any difference.

As with the myLog, it would appear the connection details are identical, the difference occurs with the queries.

Opening a table triggers two queries. The first fetches the primary key, the second then fetches the full row for each primary key.

With the initial connection and the connection after timeout, the query to fetch primary keys is identical, and correctly returns the data both times. I.e.

Q...ASELECT "admin"."code"."codeid" FROM "admin"."code" .

T..."..codeid...o6..............D..........3D..........1D... .......2D..........4C....SELECT.Z....I

(NB: There are 4 rows in this table with primary keys are 1,2,3,4)

The followup query is where the difference is.

Initial connection:

Q...:SELECT "codeid","colourcode","hexvalue","image","timestamp","userid " FROM "admin"."code" WHERE "codeid" = '3' OR "codeid" = '1' OR "codeid" = '2' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4'.

However, after a timeout and reconnection we get ...

Q...:SELECT "codeid","colourcode","hexvalue","image","timestamp","userid " FROM "admin"."code" WHERE "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.'.

Which the server then responds with an error:

E...QSERROR.C22P02.Minvalid input syntax for integer: ".".Fint8.c.L102.Rscanint8..Z....I



------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-10 11:52

Message:
I tried the latest driver 08.02.0002 but got the same problem. However I seem to of eliminated the protocol difference as significant.

With my DSN entry set to use the latest driver, I recreated the links to the tables in Access. Now myLog shows the protocol is set to '7.4-1' on both the initial connection, and any reconnections.

In fact myLog is now nearly identical for both the initial connection and the reconnection. The only difference being that the initial connection has the following additional entries:

[2244][SQLGetConnectAttrW][2244]PGAPI_GetConnectAttr 30002
[2244]PGAPI_GetConnectOption: entering...
[2244]CONN ERROR: func=PGAPI_GetConnectOption, desc='', errnum=205, errmsg='Unknown connect option (Get)'
[2244]CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
[2244][SQLSetConnectAttrW][2244]PGAPI_SetConnectAttr 30002 9a81478
[2244]PGAPI_SetConnectOption: entering fOption = 30002 vParam = 162010232
[2244]CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=205, errmsg='Unknown connect option (Set)'
[2244]Microsoft Jet !!!!





------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-07 12:02

Message:
I switched on myLog and spotted a minor difference in the connection string when it reconnects after the connection closes.

After login, in mylog produces a set of entries including the following line ...


[2952]copyAttributes: DSN='PGResearch',server='xxxxx.co.uk',dbase='research',user= 'xxxxx',passwd='xxxxx',port='5432',onlyread='0',protocol='6. 2',conn_settings='',disallow_premature=0)

.... after the connection closes, if I open a table the log shows a new connection attempt, but this time there is a minor difference ...


[2952]copyAttributes: DSN='PGResearch',server='xxxxx.co.uk',dbase='research',user= 'xxxxx',passwd='xxxxx',port='5432',onlyread='0',protocol='7. 4',conn_settings='',disallow_premature=0)


Could protocol='7.4' be significant?


------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-06 15:16

Message:
I switched on the CommLog and got the following:


I login to Access and the following appears in the log ...


CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, sqlstate=, errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=162610200, conn=162610256, status=0, num_stmts=16
conn=162610256, PGAPI_DriverConnect( in)='DSN=PGResearch;UID=xxxxxxx;PWD=xxxxxxx;DATABASE=researc h;SERVER=xxxxxxx.co.uk;PORT=5432;SSLMODE=prefer;A6=;A7=100;A 8=8192;B0=4000;B1=8190;BI=0;C2=dd_;CX=1b50389;', fDriverCompletion=0
DSN info: DSN='PGResearch',server='xxxxxxx.co.uk',port='5432',dbase='r esearch',user='xxxxxxx',passwd='xxxxx'
onlyread='0',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''


.... things then run fine, but if I leave Access for a while and then return the log show a new entry and this is when the error occurs ...


conn=162610256, PGAPI_DriverConnect( in)='DSN=PGResearch;UID=xxxxxxx;PWD=xxxxxxx;DATABASE=researc h;SERVER=xxxxxxx.co.uk;PORT=5432;SSLMODE=prefer;A6=;A7=100;A 8=8192;B0=4000;B1=8190;BI=0;C2=dd_;CX=1b50389;', fDriverCompletion=0
DSN info: DSN='PGResearch',server='xxxxxxx.co.uk',port='5432',dbase='r esearch',user='xxxxxxx',passwd='xxxxx'
onlyread='0',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''




------------------------------------------------------------ ----------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 81&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: [ psqlodbc-Bugs-1000681 ] Error when using ODBC driver with Microsoft Access

--0-1921505196-1172713878=:69020
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Is there any way to change timeout setting without changing windows regis=
try ? Or what is Access VBA command to change windows registry odbc timeo=
ut ?
=20
Thanks !

noreply@pgfoundry.org wrote:
Bugs item #1000681, was opened at 2006-07-06 13:54
You can respond by visiting:=20
http://pgfoundry.org/tracker/?func=3Ddetail&atid=3D538&aid=3 D1000681&grou=
p_id=3D1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Nobody (None)
Assigned to: Nobody (None)
Summary: Error when using ODBC driver with Microsoft Access

Initial Comment:
I have a similar problem as http://pgfoundry.org/tracker/index.php?func=3D=
detail&aid=3D1000528&group_id=3D1000125&atid=3D538, although not after a =
foreign key error, it just occurs at random multiple times a day.

I am using Access 2002, with tables link to PostgreSQL using version 8.01=
..02.00 ANSI of the ODBC driver. After logging in to Access, everything wi=
ll work without problem for some time, and then just suddenly fail.

Once the problem occurs any table I open contains the correct number of r=
ows, but ever column contains the text '#Name?'. And I will get something=
similar to the following in the postgreSQL logs:

postgres[19930]: [22-1] 2006-07-06 12:33:51 BST guycallaghan research ERR=
OR: invalid input syntax for integer: "^C"
postgres[19930]: [22-2] 2006-07-06 12:33:51 BST guycallaghan research STA=
TEMENT: SELECT=20
postgres[19930]: [22-3] "codeid","colourcode","hexvalue","image","timesta=
mp","userid" FROM "admin"."code" WHERE "codeid" =3D '^C' OR
postgres[19930]: [22-4] "codeid" =3D '^A' OR "codeid"=3D '^B' OR "codeid"=
=3D '^D' OR "codeid" =3D '^D' OR "codeid" =3D '^D' OR "codeid" =3D '^D' =
OR
postgres[19930]: [22-5] "codeid" =3D '^D' OR "codeid"=3D '^D' OR "codeid"=
=3D '^D'


Restarting Access always resolves the problem.




------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
> [url=3Dhttp://cholesterolgood.page.by/cholestae/cholestae.ht ml cholesta=
e
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- die=
t-healthy-high.html cholesterol diet healthy high
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- alt=
ernative-treatments.html cholesterol alternative treatments
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- alt=
ernative-treatment.html cholesterol alternative treatment
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/low-choleste rol=
-meal-plan.html low cholesterol meal plan
][/url]=20

------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
> [url=3Dhttp://cholesterolgood.page.by/cholestae/cholestae.ht ml cholesta=
e
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- die=
t-healthy-high.html cholesterol diet healthy high
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- alt=
ernative-treatments.html cholesterol alternative treatments
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- alt=
ernative-treatment.html cholesterol alternative treatment
][/url] > [url=3Dhttp://cholesterolgood.page.by/cholestae/low-choleste rol=
-meal-plan.html low cholesterol meal plan
][/url]=20

------------------------------------------------------------ ----------

Comment By: Greg Campbell (gregc)
Date: 2006-08-09 13:09

Message:
I highly recommend ConnectionTimeout of 0 where pgodbc is used with Jet/A=
ccess. That means never timeout. Many problems seem to disappear.

Your latest posting seems to imply a timeout factor, and a multi-thread p=
roblem, possibly a connection pooling issue. One thread seems to stay ali=
ve, while a secondary thread, used after a timeout, fails to reconnect, o=
r more correctly it is a thread that tries to reuse a connection that has=
timed out. It obviously is unaware of the timeout or the Access code is =
ill-prepared to handle the timeout on that thread. This means the only th=
ing you can do is to not allow the timeouts.

You might also consider getting an account/logging in on pgfoundry so tha=
t we have some name to address you by.



------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-08-09 09:02

Message:
Greg,


The standard procedure for Access when you open a table appears to be ...=
First it fetches a list of primary keys with one query. It then follows-=
up with a second query to fetch the actual full row data.

This procedure is the same whether the connection is the first one, or a =
re-created one after the timeout.


When I capture the network traffic, I can see the first query always work=
s, irrespective of whether it is the intital database connection, or one =
re-connected after a timeout.

It is the second query that fails when we have a re-connected connection.


The default setting for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engi=
nes\ODBC\Connect
ionTimeout is 600 (i.e. 10 minutes). I have increased ours to 3600 (i.e. =
one hour).






------------------------------------------------------------ ----------

Comment By: Greg Campbell (gregc)
Date: 2006-08-07 14:42

Message:
You indicate a time factor, and after that Access sends these querie do n=
ot use the primary keys and queries each row by all possible values. Yes?

What is your setting for
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\Co nnectionTimeo=
ut



------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-08-07 11:24

Message:
Hi Luf,

I eliminated the protocol change (see comment 2006-07-10 11:52), but the =
problem still occurred.

I will try the latest dll as suggested.


For anybody else suffering with this problem, you can reduce the impact b=
y extending the Jet ODBC connection timeout by editing the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\C onnectionTime=
out



------------------------------------------------------------ ----------

Comment By: Ludek Finstrle (luf)
Date: 2006-07-24 10:49

Message:
Hello,

the change from 6.4 to 7.4 protocol is significant. You should use 7.4.

Could you try the latest dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?
If the problem still persist it would be nice you post us the mylog outpu=
t (maybe only the problematic part - it depends on mylog size).

Regards,

Luf

------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-12 11:18

Message:
I captured the network traffic to see if I can spot any difference.

As with the myLog, it would appear the connection details are identical, =
the difference occurs with the queries.

Opening a table triggers two queries. The first fetches the primary key, =
the second then fetches the full row for each primary key.

With the initial connection and the connection after timeout, the query t=
o fetch primary keys is identical, and correctly returns the data both ti=
mes. I.e.

Q...ASELECT "admin"."code"."codeid" FROM "admin"."code" .

T..."..codeid...o6..............D..........3D..........1D... .......2D....=
.......4C....SELECT.Z....I

(NB: There are 4 rows in this table with primary keys are 1,2,3,4)

The followup query is where the difference is.

Initial connection:

Q...:SELECT "codeid","colourcode","hexvalue","image","timestamp","userid "=
FROM "admin"."code" WHERE "codeid" =3D '3' OR "codeid" =3D '1' OR "codei=
d" =3D '2' OR "codeid" =3D '4' OR "codeid" =3D '4' OR "codeid" =3D '4' OR=
"codeid" =3D '4' OR "codeid" =3D '4' OR "codeid" =3D '4' OR "codeid" =3D=
'4'.

However, after a timeout and reconnection we get ...

Q...:SELECT "codeid","colourcode","hexvalue","image","timestamp","userid "=
FROM "admin"."code" WHERE "codeid" =3D '.' OR "codeid" =3D '.' OR "codei=
d" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D '.' OR=
"codeid" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D=
'.'.

Which the server then responds with an error:

E...QSERROR.C22P02.Minvalid input syntax for integer: ".".Fint8.c.L102.Rs=
canint8..Z....I



------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-10 11:52

Message:
I tried the latest driver 08.02.0002 but got the same problem. However I =
seem to of eliminated the protocol difference as significant.

With my DSN entry set to use the latest driver, I recreated the links to =
the tables in Access. Now myLog shows the protocol is set to '7.4-1' on b=
oth the initial connection, and any reconnections.

In fact myLog is now nearly identical for both the initial connection and=
the reconnection. The only difference being that the initial connection =
has the following additional entries:

[2244][SQLGetConnectAttrW][2244]PGAPI_GetConnectAttr 30002
[2244]PGAPI_GetConnectOption: entering...
[2244]CONN ERROR: func=3DPGAPI_GetConnectOption, desc=3D'', errnum=3D205,=
errmsg=3D'Unknown connect option (Get)'
[2244]CONN ERROR: func=3DPGAPI_GetConnectOption, desc=3D'fOption=3D30002'=
, errnum=3D205, errmsg=3D'Unknown connect option (Get)'
[2244][SQLSetConnectAttrW][2244]PGAPI_SetConnectAttr 30002 9a81478
[2244]PGAPI_SetConnectOption: entering fOption =3D 30002 vParam =3D 16201=
0232
[2244]CONN ERROR: func=3DPGAPI_SetConnectOption, desc=3D'', errnum=3D205,=
errmsg=3D'Unknown connect option (Set)'
[2244]Microsoft Jet !!!!





------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-07 12:02

Message:
I switched on myLog and spotted a minor difference in the connection stri=
ng when it reconnects after the connection closes.

After login, in mylog produces a set of entries including the following l=
ine ...


[2952]copyAttributes: DSN=3D'PGResearch',server=3D'xxxxx.co.uk',dbase=3D'=
research',user=3D'xxxxx',passwd=3D'xxxxx',port=3D'5432',only read=3D'0',pr=
otocol=3D'6.2',conn_settings=3D'',disallow_premature=3D0)

.... after the connection closes, if I open a table the log shows a new co=
nnection attempt, but this time there is a minor difference ...


[2952]copyAttributes: DSN=3D'PGResearch',server=3D'xxxxx.co.uk',dbase=3D'=
research',user=3D'xxxxx',passwd=3D'xxxxx',port=3D'5432',only read=3D'0',pr=
otocol=3D'7.4',conn_settings=3D'',disallow_premature=3D0)


Could protocol=3D'7.4' be significant?


------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-07-06 15:16

Message:
I switched on the CommLog and got the following:


I login to Access and the following appears in the log ...


CONN ERROR: func=3DPGAPI_GetConnectOption, desc=3D'fOption=3D30002', errn=
um=3D205, sqlstate=3D, errmsg=3D'Unknown connect option (Get)'
------------------------------------------------------------
henv=3D162610200, conn=3D162610256, status=3D0, num_stmts=3D16
conn=3D162610256, PGAPI_DriverConnect( in)=3D'DSN=3DPGResearch;UID=3Dxxxx=
xxx;PWD=3Dxxxxxxx;DATABASE=3Dresearch;SERVER=3Dxxxxxxx.co.uk ;PORT=3D5432;=
SSLMODE=3Dprefer;A6=3D;A7=3D100;A8=3D8192;B0=3D4000;B1=3D819 0;BI=3D0;C2=3D=
dd_;CX=3D1b50389;', fDriverCompletion=3D0
DSN info: DSN=3D'PGResearch',server=3D'xxxxxxx.co.uk',port=3D'5432',db ase=
=3D'research',user=3D'xxxxxxx',passwd=3D'xxxxx'
onlyread=3D'0',showoid=3D'0',fakeoidindex=3D'0',showsystable =3D'0'
conn_settings=3D'',conn_encoding=3D'OTHER'
translation_dll=3D'',translation_option=3D''


.... things then run fine, but if I leave Access for a while and then retu=
rn the log show a new entry and this is when the error occurs ...


conn=3D162610256, PGAPI_DriverConnect( in)=3D'DSN=3DPGResearch;UID=3Dxxxx=
xxx;PWD=3Dxxxxxxx;DATABASE=3Dresearch;SERVER=3Dxxxxxxx.co.uk ;PORT=3D5432;=
SSLMODE=3Dprefer;A6=3D;A7=3D100;A8=3D8192;B0=3D4000;B1=3D819 0;BI=3D0;C2=3D=
dd_;CX=3D1b50389;', fDriverCompletion=3D0
DSN info: DSN=3D'PGResearch',server=3D'xxxxxxx.co.uk',port=3D'5432',db ase=
=3D'research',user=3D'xxxxxxx',passwd=3D'xxxxx'
onlyread=3D'0',showoid=3D'0',fakeoidindex=3D'0',showsystable =3D'0'
conn_settings=3D'',conn_encoding=3D'OTHER'
translation_dll=3D'',translation_option=3D''




------------------------------------------------------------ ----------

You can respond by visiting:=20
http://pgfoundry.org/tracker/?func=3Ddetail&atid=3D538&aid=3 D1000681&grou=
p_id=3D1000125

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


=20
---------------------------------
No need to miss a message. Get email on-the-go=20
with Yahoo! Mail for Mobile. Get started.
--0-1921505196-1172713878=:69020
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

yle=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px soli=
d">Bugs item #1000681, was opened at 2006-07-06 13:54
You can respond =
by visiting:
http://pgfoundry.org/tracker/?func=3Ddetail&atid=3D5=
38&aid=3D1000681&group_id=3D1000125

Category: None
Grou=
p: None
Status: Open
Resolution: None
Priority: 3
Submitted B=
y: Nobody (None)
Assigned to: Nobody (None)
Summary: Error when usi=
ng ODBC driver with Microsoft Access

Initial Comment:
I have a =
similar problem as http://pgfoundry.org/tracker/index.php?func=3Ddetail&a=
mp;aid=3D1000528&group_id=3D1000125&atid=3D538, although not afte=
r a foreign key error, it just occurs at random multiple
times a day.

I am using Access 2002, with tables link to PostgreS=
QL using version 8.01.02.00 ANSI of the ODBC driver. After logging in to =
Access, everything will work without problem for some time, and then just=
suddenly fail.

Once the problem occurs any table I open contains =
the correct number of rows, but ever column contains the text '#Name?'. A=
nd I will get something similar to the following in the postgreSQL logs:<=
BR>
postgres[19930]: [22-1] 2006-07-06 12:33:51 BST guycallaghan resea=
rch ERROR: invalid input syntax for integer: "^C"
postgres[19930]: [22=
-2] 2006-07-06 12:33:51 BST guycallaghan research STATEMENT: SELECT
p=
ostgres[19930]: [22-3] "codeid","colourcode","hexvalue","image","timestam=
p","userid" FROM "admin"."code" WHERE "codeid" =3D '^C' OR
postgres[19=
930]: [22-4] "codeid" =3D '^A' OR "codeid"=3D '^B' OR "codeid" =3D '^D' O=
R "codeid" =3D '^D' OR "codeid" =3D '^D' OR "codeid" =3D '^D' OR
postg=
res[19930]: [22-5] "codeid" =3D '^D' OR "codeid"=3D '^D'
OR "codeid" =3D '^D'


Restarting Access always resolves the pr=
oblem.




-----------------------------------------------=
-----------------------

Comment By: Nobody (None)
Date: 2007-02=
-28 00:20

Message:
olestae/cholestae.html" cholestae
> [url=3Dhttp://cholesterolgo=
od.page.by/cholestae/cholestae.html cholestae
][/url] //cholesterolgood.page.by/cholestae/cholesterol-diet-healthy -high.html" h=
igh
> [url=3Dhttp://cholesterolgood.pa=
ge.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healt=
hy high
][/url] olesterol-alternative-treatments.html" cholesterol treatments
ive>> [url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- =
alternative-treatments.html cholesterol alternative treatments
][/url]=
href=3D"http://cholesterolgood.page.by/cholestae/cholesterol -alternative=
-treatment.html" cholesterol alternative treatment
> [url=3Dhtt=
p://cholesterolgood.page.by/cholestae/cholesterol-alternativ e-treatment.h=
tml cholesterol alternative treatment
][/url] terolgood.page.by/cholestae/low-cholesterol-meal-plan.html" cholesterol p=
lan
> [url=3Dhttp://cholesterolgood.page.by/cholestae/=
low-cholesterol-meal-plan.html low cholesterol meal plan
][/url]
<=
BR>--------------------------------------------------------- -------------=


Comment By: Nobody (None)
Date: 2007-02-28 00:20

Messag=
e:
cholestae
> [url=3Dhttp://cholesterolgood.page.by/cholestae/ch=
olestae.html cholestae
][/url] by/cholestae/cholesterol-diet-healthy-high.html" high
lesterol>>
[url=3Dhttp://cholesterolgood.page.by/cholestae/cholesterol- diet-healthy=
-high.html cholesterol diet healthy high
][/url] lesterolgood.page.by/cholestae/cholesterol-alternative-treat ments.html" c=
holesterol treatments
> [url=3Dhttp://cholesterolgo=
od.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol =
alternative treatments
][/url] by/cholestae/cholesterol-alternative-treatment.html" cholesterol alternat=
ive treatment
> [url=3Dhttp://cholesterolgood.page.by/cholestae=
/cholesterol-alternative-treatment.html cholesterol alternative treatment=

][/url] sterol-meal-plan.html" cholesterol plan
> [url=3Dhttp:=
//cholesterolgood.page.by/cholestae/low-cholesterol-meal-pla n.html low ch=
olesterol meal plan
][/url]

----------------------------------=
------------------------------------

Comment
By: Greg Campbell (gregc)
Date: 2006-08-09 13:09

Message:
I=
highly recommend ConnectionTimeout of 0 where pgodbc is used with Jet/Ac=
cess. That means never timeout. Many problems seem to disappear.

Y=
our latest posting seems to imply a timeout factor, and a multi-thread pr=
oblem, possibly a connection pooling issue. One thread seems to stay aliv=
e, while a secondary thread, used after a timeout, fails to reconnect, or=
more correctly it is a thread that tries to reuse a connection that has =
timed out. It obviously is unaware of the timeout or the Access code is i=
ll-prepared to handle the timeout on that thread. This means the only thi=
ng you can do is to not allow the timeouts.

You might also conside=
r getting an account/logging in on pgfoundry so that we have some name to=
address you by.



-----------------------------------------=
-----------------------------

Comment By: Nobody (None)
Date: 2=
006-08-09
09:02

Message:
Greg,


The standard procedure for Acc=
ess when you open a table appears to be ... First it fetches a list of pr=
imary keys with one query. It then follows-up with a second query to fetc=
h the actual full row data.

This procedure is the same whether the=
connection is the first one, or a re-created one after the timeout.
<=
BR>
When I capture the network traffic, I can see the first query alwa=
ys works, irrespective of whether it is the intital database connection, =
or one re-connected after a timeout.

It is the second query that f=
ails when we have a re-connected connection.


The default setti=
ng for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\Co nnect<=
BR>ionTimeout is 600 (i.e. 10 minutes). I have increased ours to 3600 (i.=
e. one hour).






--------------------------------=
--------------------------------------

Comment By: Greg Campbell (=
gregc)
Date: 2006-08-07
14:42

Message:
You indicate a time factor, and after that Acce=
ss sends these querie do not use the primary keys and queries each row by=
all possible values. Yes?

What is your setting for
HKEY_LOCAL_=
MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\ConnectionTim eout

<=
BR>
------------------------------------------------------------ ------=
----

Comment By: Nobody (None)
Date: 2006-08-07 11:24

Me=
ssage:
Hi Luf,

I eliminated the protocol change (see comment 20=
06-07-10 11:52), but the problem still occurred.

I will try the la=
test dll as suggested.


For anybody else suffering with this pr=
oblem, you can reduce the impact by extending the Jet ODBC connection tim=
eout by editing the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsof=
t\Jet\4.0\Engines\ODBC\ConnectionTimeout



-----------------=
-----------------------------------------------------

Comment By: =
Ludek Finstrle (luf)
Date:
2006-07-24 10:49

Message:
Hello,

the change from 6.4 to=
7.4 protocol is significant. You should use 7.4.

Could you try th=
e latest dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.h=
tml
?
If the problem still persist it would be nice you post us the=
mylog output (maybe only the problematic part - it depends on mylog size=
).

Regards,

Luf

------------------------------------=
----------------------------------

Comment By: Nobody (None)
Da=
te: 2006-07-12 11:18

Message:
I captured the network traffic to=
see if I can spot any difference.

As with the myLog, it would app=
ear the connection details are identical, the difference occurs with the =
queries.

Opening a table triggers two queries. The first fetches t=
he primary key, the second then fetches the full row for each primary key=
..

With the initial connection and the connection after timeout, th=
e query to fetch primary keys is
identical, and correctly returns the data both times. I.e.

Q...AS=
ELECT "admin"."code"."codeid" FROM "admin"."code" .

T..."..codeid.=
...o6..............D..........3D..........1D..........2D.... ......4C....SE=
LECT.Z....I

(NB: There are 4 rows in this table with primary keys =
are 1,2,3,4)

The followup query is where the difference is.
>Initial connection:

Q...:SELECT "codeid","colourcode","hexvalue",=
"image","timestamp","userid" FROM "admin"."code" WHERE "codeid" =3D '3' O=
R "codeid" =3D '1' OR "codeid" =3D '2' OR "codeid" =3D '4' OR "codeid" =3D=
'4' OR "codeid" =3D '4' OR "codeid" =3D '4' OR "codeid" =3D '4' OR "code=
id" =3D '4' OR "codeid" =3D '4'.

However, after a timeout and reco=
nnection we get ...

Q...:SELECT "codeid","colourcode","hexvalue","=
image","timestamp","userid" FROM "admin"."code" WHERE "codeid" =3D '.' OR=
"codeid" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D=
'.' OR "codeid" =3D '.' OR "codeid" =3D '.' OR "codeid" =3D '.' OR
"codeid" =3D '.' OR "codeid" =3D '.'.

Which the server then respo=
nds with an error:

E...QSERROR.C22P02.Minvalid input syntax for in=
teger: ".".Fint8.c.L102.Rscanint8..Z....I



----------------=
------------------------------------------------------

Comment By:=
Nobody (None)
Date: 2006-07-10 11:52

Message:
I tried the l=
atest driver 08.02.0002 but got the same problem. However I seem to of el=
iminated the protocol difference as significant.

With my DSN entry=
set to use the latest driver, I recreated the links to the tables in Acc=
ess. Now myLog shows the protocol is set to '7.4-1' on both the initial c=
onnection, and any reconnections.

In fact myLog is now nearly iden=
tical for both the initial connection and the reconnection. The only diff=
erence being that the initial connection has the following additional ent=
ries:

[2244][SQLGetConnectAttrW][2244]PGAPI_GetConnectAttr 30002 R>[2244]PGAPI_GetConnectOption:
entering...
[2244]CONN ERROR: func=3DPGAPI_GetConnectOption, desc=3D'=
', errnum=3D205, errmsg=3D'Unknown connect option (Get)'
[2244]CONN ER=
ROR: func=3DPGAPI_GetConnectOption, desc=3D'fOption=3D30002', errnum=3D20=
5, errmsg=3D'Unknown connect option (Get)'
[2244][SQLSetConnectAttrW][=
2244]PGAPI_SetConnectAttr 30002 9a81478
[2244]PGAPI_SetConnectOption: =
entering fOption =3D 30002 vParam =3D 162010232
[2244]CONN ERROR: func=
=3DPGAPI_SetConnectOption, desc=3D'', errnum=3D205, errmsg=3D'Unknown con=
nect option (Set)'
[2244]Microsoft Jet !!!!





---=
------------------------------------------------------------ -------
R>Comment By: Nobody (None)
Date: 2006-07-07 12:02

Message:
=
I switched on myLog and spotted a minor difference in the connection stri=
ng when it reconnects after the connection closes.

After login, in=
mylog produces a set of entries including the following line ...

=

[2952]copyAttributes:
DSN=3D'PGResearch',server=3D'xxxxx.co.uk',dbase=3D'research' ,user=3D'xxx=
xx',passwd=3D'xxxxx',port=3D'5432',onlyread=3D'0',protocol=3 D'6.2',conn_s=
ettings=3D'',disallow_premature=3D0)

... after the connection clos=
es, if I open a table the log shows a new connection attempt, but this ti=
me there is a minor difference ...


[2952]copyAttributes: DSN=3D=
'PGResearch',server=3D'xxxxx.co.uk',dbase=3D'research',user= 3D'xxxxx',pas=
swd=3D'xxxxx',port=3D'5432',onlyread=3D'0',protocol=3D'7.4', conn_settings=
=3D'',disallow_premature=3D0)


Could protocol=3D'7.4' be signif=
icant?


-------------------------------------------------------=
---------------

Comment By: Nobody (None)
Date: 2006-07-06 15:1=
6

Message:
I switched on the CommLog and got the following:
=


I login to Access and the following appears in the log ...
>
CONN ERROR: func=3DPGAPI_GetConnectOption, desc=3D'fOption=3D30002',=
errnum=3D205, sqlstate=3D, errmsg=3D'Unknown connect option
(Get)'
------------------------------------------------------------ R>henv=3D162610200, conn=3D162610256, status=3D0, num_stmts=3D16
conn=3D=
162610256, PGAPI_DriverConnect( in)=3D'DSN=3DPGResearch;UID=3Dxxxxxxx;PWD=
=3Dxxxxxxx;DATABASE=3Dresearch;SERVER=3Dxxxxxxx.co.uk;PORT=3 D5432;SSLMODE=
=3Dprefer;A6=3D;A7=3D100;A8=3D8192;B0=3D4000;B1=3D8190;BI=3D 0;C2=3Ddd_;CX=
=3D1b50389;', fDriverCompletion=3D0
DSN info: DSN=3D'PGResearch',serve=
r=3D'xxxxxxx.co.uk',port=3D'5432',dbase=3D'research',user=3D 'xxxxxxx',pas=
swd=3D'xxxxx'
onlyread=3D'0',showoid=3D'0',fakeoidindex=3D'0',showsyst=
able=3D'0'
conn_settings=3D'',conn_encoding=3D'OTHER'
translation_d=
ll=3D'',translation_option=3D''


... things then run fine, but =
if I leave Access for a while and then return the log show a new entry an=
d this is when the error occurs ...


conn=3D162610256, PGAPI_Dr=
iverConnect( in)=3D'DSN=3DPGResearch;UID=3Dxxxxxxx;PWD=3Dxxxxxxx;DATABASE =
=3Dresearch;SERVER=3Dxxxxxxx.co.uk;PORT=3D5432;SSLMODE=3Dpre fer;A6=3D;A7=3D=
100;A8=3D8192;B0=3D4000;B1=3D8190;BI=3D0;C2=3Ddd_;CX=3D1b503 89;',
fDriverCompletion=3D0
DSN info: DSN=3D'PGResearch',server=3D'xxxxxxx.=
co.uk',port=3D'5432',dbase=3D'research',user=3D'xxxxxxx',pas swd=3D'xxxxx'=

onlyread=3D'0',showoid=3D'0',fakeoidindex=3D'0',showsystable =3D'0' >conn_settings=3D'',conn_encoding=3D'OTHER'
translation_dll=3D'',trans=
lation_option=3D''




-----------------------------------=
-----------------------------------

You can respond by visiting: <=
BR>http://pgfoundry.org/tracker/?func=3Ddetail&atid=3D53 8&aid=3D1=
000681&group_id=3D1000125

---------------------------(end of b=
roadcast)---------------------------
TIP 1: if posting/reading through=
Usenet, please send an appropriate
subscribe-nomail command to majord=
omo@postgresql.org so that your
message can get through to the mailing=
list cleanly